package fy.hr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.hr.bean.expertsbean;
import fy.util.DBUtil;

public class expertsDao {
	private static Log log = LogFactory.getLog(expertsDao.class);
	private String[] codeArray;
	expertsbean csb = new expertsbean();
	private final static String SELECTCODE = "select f_155 ,PSNCODE  from v_data  f where PSNCODE= ? and CYEAR||CPERIOD in (select max(CYEAR||CPERIOD) from v_data where PSNCODE=f.PSNCODE )";
	// private final static String
	// SELECTCODE="select pk_psndoc,psncode from bd_psndoc where psnclscope='0' and psncode=?";
	// 出差
	private final static String AWAYB = "select * from tbm_awayb where psncode=(select pk_psndoc from bd_psndoc where psnclscope='0' and psncode=?) and to_char(sysdate,'YYYY-MM-DD') between awaybeginday and awayendday";
	// 休假
	private final static String LEAVEB = "select * from tbm_leaveb where pk_tbm_psndoc=(select pk_psndoc from bd_psndoc where psnclscope='0' and psncode=?) and to_char(sysdate,'YYYY-MM-DD') between leavebeginday and leaveendday";

	public JSONObject csCode() throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		JSONObject jo = new JSONObject();
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());
			pst = conn.prepareStatement(SELECTCODE);
			for (int i = 0; i < codeArray.length; i++) {
				pst.setString(1, codeArray[i]);
			}
			if (away() == true) {
				csb.setState("出差");
			} else if (leave() == true) {
				csb.setState("休假");
			} else {
				csb.setState("正常上班");
			}
			rs = pst.executeQuery();

			if (rs.next()) {
				csb.setJob(rs.getString(1));
				csb.setCode(rs.getString(2));
			}
			jo.put(csb.getID(), csb.getJSONObject());
			// System.out.println(jo+"2");
			log.info(jo);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return jo;

	}

	public boolean away() {// 出差
		boolean state = false;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());
			pst = conn.prepareStatement(AWAYB);
			for (int i = 0; i < codeArray.length; i++) {
				pst.setString(1, codeArray[i]);
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				state = true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return state;
	}

	public boolean leave() {// 休假
		boolean state = false;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());
			pst = conn.prepareStatement(LEAVEB);
			for (int i = 0; i < codeArray.length; i++) {
				pst.setString(1, codeArray[i]);
			}
			rs = pst.executeQuery();
			while (rs.next()) {
				state = true;
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return state;
	}

	public String[] getCodeArray() {
		return codeArray;
	}

	public void setCodeArray(String[] codeArray) {
		this.codeArray = codeArray;
	}
}
